﻿Public Class clsProdInfo

    Public Function getProdInfoListByTrayNoAndStatus(tray_no As String, status As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT * FROM sf.b_prod_info WHERE 1=1 and tray_no = ? and status = ?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, tray_no, status)
        Return dt
    End Function

    Public Function getProdInfoListByYzzPgdId(pgd_yzz_sys_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT count(*) as zx_count FROM sf.b_prod_info WHERE 1=1 and b_pgd_yzz_details_sys_id = ? "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, pgd_yzz_sys_id)
        Return dt
    End Function

    Public Function getProdInfoListByStatus(status As String) As DataTable
        Dim szSql As String = "SELECT a.*,b.mes_project_name,b.mes_project_code,b.support_model,c.prod_spec_name,b.oper_count,b.type_value,b.finish_count "
        szSql &= " FROM sf.b_prod_info a "
        szSql &= " left join sf.b_pgd_mes b on a.pgd_id::integer=b.sys_id "
        szSql &= " left join sf.t_prod_spec c on b.support_model=c.prod_spec_id "
        szSql &= " WHERE a.status = ?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, status)
        Return dt
    End Function

    Public Function insertProdInfo(prod_id As String, pgd_id As String, support_model As String, tray_no As String, flb_sys_id As String, flb_dl_no As String, steel_seal_no As String, project_no As String, oper As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            '添加制品信息
            szSql &= "INSERT INTO sf.b_prod_info(factory_id, prod_id, pgd_id, prod_spec_id, b_pgd_yzz_details_sys_id, tray_no, flb_sys_id, flb_dl_no, status, steel_seal_no, project_no, yz_ope, yz_time, update_time)"
            szSql &= "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)), (SELECT CURRENT_TIMESTAMP(6))) "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "TS", prod_id, pgd_id, support_model, pgd_id, tray_no, flb_sys_id, flb_dl_no, "0", steel_seal_no, project_no, oper)
            '修改法兰板、吊篮状态为已使用
            szSql = String.Empty
            szSql &= "update sf.b_flb_info set used_piece_num=used_piece_num+1,status=?,used_date=(SELECT CURRENT_TIMESTAMP(6)),update_time=(SELECT CURRENT_TIMESTAMP(6))"
            szSql &= "where sys_id=?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", flb_sys_id)

            szSql = String.Empty
            szSql &= "select * from sf.b_flb_info where sys_id= ?"
            Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, flb_sys_id)
            szSql = String.Empty
            szSql &= "update sf.t_mst_dl set status = ? where dl_no = ? "
            If dt.Rows(0)("piece_num") = dt.Rows(0)("used_piece_num") Then
                '更新法兰板吊篮主档忙闲字段
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "0", flb_dl_no)
                '修改法兰板、吊篮状态为已使用
                szSql = String.Empty
                szSql &= "update sf.b_flb_info set status=?,used_date=(SELECT CURRENT_TIMESTAMP(6)),update_time=(SELECT CURRENT_TIMESTAMP(6))"
                szSql &= "where sys_id=?"
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "2", flb_sys_id)
            ElseIf Int32.Parse(dt.Rows(0)("piece_num")) - Int32.Parse(dt.Rows(0)("used_piece_num")) = 1 Then
                '更新法兰板吊篮主档忙闲字段
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "2", flb_dl_no)
            End If

            szSql = String.Empty
            szSql &= "select * from sf.b_flb_ck where dl_no = ? and status = ? "
            Dim dtflbck As DataTable = DBacs.ExecuteSql(szSql, -1, flb_dl_no, "0")
            If dtflbck.Rows.Count = 1 Then
                szSql = String.Empty
                szSql &= "update sf.b_flb_ck set status = ?, end_date = (SELECT CURRENT_TIMESTAMP(6)),out_ope = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) where sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", oper, dtflbck.Rows(0)("sys_id"))
            End If

            '更新托盘主档忙闲字段
            szSql = String.Empty
            szSql &= "update sf.t_mst_tp set "
            szSql &= " status = ? "
            szSql &= " where tp_no = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", tray_no)

            DBacs.CommitTransaction()
        Catch ex As Exception
            rtn = -2
            DBacs.RollbackTransaction()
        End Try

        Return rtn
    End Function

    ' 更新预组装信息
    Public Function updateProdInfo(prodId As String, gjb_sys_id As String, gjb_dl_no As String, jp_all_wt As String, jp_type As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= " update sf.b_prod_info set status = ?, "
            szSql &= " gjb_sys_id = ?, gjb_dl_no = ?, jp_all_wt = ?, jp_type = ?, "
            szSql &= " update_time = (SELECT CURRENT_TIMESTAMP(6))"
            szSql &= " where prod_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", gjb_sys_id, gjb_dl_no, jp_all_wt, jp_type, prodId)
            '更新法兰板吊篮主档忙闲字段
            szSql = "update sf.t_mst_dl set status = ?,update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= "where dl_no = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "0", gjb_dl_no.Substring(0, gjb_dl_no.Length - 1))
            '骨架板状态变更 
            szSql = "update sf.b_gjb_dl_info "
            szSql &= "set status = ?, used_date = (SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= "where sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "2", gjb_sys_id)
            DBacs.CommitTransaction()
        Catch ex As Exception
            rtn = -2
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function

    Public Function updateProdInfoJpAllWt(prodId As String, jp_all_wt As String)
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= " update sf.b_prod_info set jp_all_wt = ? where prod_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, jp_all_wt, prodId)
            DBacs.CommitTransaction()
        Catch ex As Exception
            rtn = -2
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function
End Class
